import pymysql

class MySqlHelper:
    def __init__(self, host, port, user, password, db):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.db = db
        self.conn = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password, db=self.db)
        self.cursor = self.conn.cursor()

    def create_table(self, table_name, fields):
        try:
            create_table_query = f"CREATE TABLE IF NOT EXISTS {table_name} ({fields})"
            self.cursor.execute(create_table_query)
            self.conn.commit()
            print(f"Table '{table_name}' created successfully.")
        except Exception as e:
            print(f"Error creating table '{table_name}': {str(e)}")
            self.conn.rollback()

    def insert(self, table_name, data):
        try:
            keys = ', '.join(data.keys())
            values = ', '.join(['%s'] * len(data))
            insert_query = f"INSERT IGNORE INTO {table_name} ({keys}) VALUES ({values})"
            self.cursor.execute(insert_query, tuple(data.values()))
            self.conn.commit()
            insertedId = self.cursor.lastrowid  # 获取插入后的自增ID
            print(f"Data inserted into table '{table_name}' successfully.")
            return insertedId
        except Exception as e:
            print(f"Error inserting data into table '{table_name}': {str(e)}")
            self.conn.rollback()
            return None

    def delete(self, table_name, condition):
        try:
            delete_query = f"DELETE FROM {table_name} WHERE {condition}"
            self.cursor.execute(delete_query)
            self.conn.commit()
            print(f"Data deleted from table '{table_name}' successfully.")
        except Exception as e:
            print(f"Error deleting data from table '{table_name}': {str(e)}")
            self.conn.rollback()

    def update(self, table_name, data, condition):
        try:
            set_values = ', '.join([f"{key}=%s" for key in data])
            update_query = f"UPDATE {table_name} SET {set_values} WHERE {condition}"
            self.cursor.execute(update_query, tuple(data.values()))
            self.conn.commit()
            print(f"Data updated in table '{table_name}' successfully.")
        except Exception as e:
            print(f"Error updating data in table '{table_name}': {str(e)}")
            self.conn.rollback()

    def select(self, table_name, fields, condition='', group_by='', order_by='', limit=None, offset=None):
        try:
            # 构造 SELECT 查询语句
            select_query = f"SELECT {fields} FROM {table_name}"
            
            # 添加 WHERE 条件
            if condition:
                select_query += f" WHERE {condition}"
            
            # 添加 GROUP BY 子句
            if group_by:
                select_query += f" GROUP BY {group_by}"
            
            # 添加 ORDER BY 子句
            if order_by:
                select_query += f" ORDER BY {order_by}"
            
            # 添加 LIMIT 限制
            if limit:
                select_query += f" LIMIT {limit}"
            
            # 添加 OFFSET 偏移
            if offset:
                select_query += f" OFFSET {offset}"
            
            # 执行 SELECT 查询
            self.cursor.execute(select_query)
            
            # 获取查询结果
            result = self.cursor.fetchall()
            
            # 返回结果
            return result
        except Exception as e:
            # 输出错误信息
            print(f"Error selecting data from table '{table_name}': {str(e)}")
            
            # 返回空值
            return None
            
    def getBySql(self, sql):
        try:
            # 执行 SELECT 查询
            self.cursor.execute(sql)
            
            # 获取查询结果
            result = self.cursor.fetchall()
            return result
        except Exception as e:
            print(f"Error selecting data, sql: '{sql}'")
            return None

    def close(self):
        self.cursor.close()
        self.conn.close()
        

# 实例化MySQL Helper对象
helper = MySqlHelper('mysql-tramper.sqlpub.com', 3306, 'tookit-tramper', '5ff868f73852edae-tramper', 'tookit-tramper')

# 创建表（示例）
fields = "id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT"
helper.create_table('users', fields)

# 插入数据（示例）
data = {'name': 'John', 'age': 30}
helper.insert('users', data)

# 删除数据（示例）
condition = 'age > 25'
helper.delete('users', condition)

# 更新数据（示例）
data = {'name': 'Tom'}
condition = 'age < 30'
helper.update('users', data, condition)

# 查询数据（示例）
fields = 'name, age'
condition = 'age > 20'
result = helper.select('users', fields, condition)
if result:
    for row in result:
        print(row)

# 关闭连接
helper.close()